/*
 Navicat Premium Data Transfer

 Source Server         : NutritionMate
 Source Server Type    : MySQL
 Source Server Version : 90200 (9.2.0)
 Source Host           : localhost:3306
 Source Schema         : NutritionMateBase

 Target Server Type    : MySQL
 Target Server Version : 90200 (9.2.0)
 File Encoding         : 65001

 Date: 14/03/2025 11:10:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for meal_nutrition
-- ----------------------------
DROP TABLE IF EXISTS `meal_nutrition`;
CREATE TABLE `meal_nutrition` (
  `record_id` bigint NOT NULL,
  `nutrition_id` int NOT NULL,
  `amount` double DEFAULT NULL,
  PRIMARY KEY (`record_id`,`nutrition_id`),
  KEY `nutrition_id` (`nutrition_id`),
  CONSTRAINT `nutrition_id` FOREIGN KEY (`nutrition_id`) REFERENCES `nutrition_facts` (`nutri_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `record_id` FOREIGN KEY (`record_id`) REFERENCES `meal_records` (`record_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of meal_nutrition
-- ----------------------------
BEGIN;
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (1, 1, 500);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (1, 2, 50);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (1, 3, 50);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (1, 4, 120);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (2, 1, 300);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (2, 2, 25);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (2, 3, 25);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (2, 4, 100);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (3, 1, 500);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (3, 2, 50);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (3, 3, 50);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (3, 4, 120);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (4, 1, 500);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (4, 2, 50);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (4, 3, 50);
INSERT INTO `meal_nutrition` (`record_id`, `nutrition_id`, `amount`) VALUES (4, 4, 120);
COMMIT;

-- ----------------------------
-- Table structure for meal_records
-- ----------------------------
DROP TABLE IF EXISTS `meal_records`;
CREATE TABLE `meal_records` (
  `record_id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint DEFAULT NULL,
  `meal_id` int DEFAULT NULL,
  `quantity` double DEFAULT NULL,
  PRIMARY KEY (`record_id`),
  KEY `meal_id` (`meal_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `meal_id` FOREIGN KEY (`meal_id`) REFERENCES `meals` (`meal_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of meal_records
-- ----------------------------
BEGIN;
INSERT INTO `meal_records` (`record_id`, `user_id`, `meal_id`, `quantity`) VALUES (1, 1, 1, NULL);
INSERT INTO `meal_records` (`record_id`, `user_id`, `meal_id`, `quantity`) VALUES (2, 1, 1, NULL);
INSERT INTO `meal_records` (`record_id`, `user_id`, `meal_id`, `quantity`) VALUES (3, 1, 2, NULL);
INSERT INTO `meal_records` (`record_id`, `user_id`, `meal_id`, `quantity`) VALUES (4, 1, 5, NULL);
COMMIT;

-- ----------------------------
-- Table structure for meals
-- ----------------------------
DROP TABLE IF EXISTS `meals`;
CREATE TABLE `meals` (
  `meal_id` int NOT NULL AUTO_INCREMENT,
  `meal_type` tinyint DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`meal_id`),
  KEY `meal_id` (`meal_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of meals
-- ----------------------------
BEGIN;
INSERT INTO `meals` (`meal_id`, `meal_type`, `date`) VALUES (1, 0, '2025-02-21');
INSERT INTO `meals` (`meal_id`, `meal_type`, `date`) VALUES (2, 1, '2025-02-21');
INSERT INTO `meals` (`meal_id`, `meal_type`, `date`) VALUES (3, 2, '2025-02-21');
INSERT INTO `meals` (`meal_id`, `meal_type`, `date`) VALUES (4, 3, '2025-02-21');
INSERT INTO `meals` (`meal_id`, `meal_type`, `date`) VALUES (5, 1, '2025-02-22');
COMMIT;

-- ----------------------------
-- Table structure for nutrition_facts
-- ----------------------------
DROP TABLE IF EXISTS `nutrition_facts`;
CREATE TABLE `nutrition_facts` (
  `nutri_id` int NOT NULL AUTO_INCREMENT,
  `nutri_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `nutri_name_en` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `nutrition_id` int NOT NULL,
  `nutrition_name` varchar(255) DEFAULT NULL,
  `nutrition_name_en` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`nutri_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of nutrition_facts
-- ----------------------------
BEGIN;
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (1, '热量', 'Calories', 'kcal', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (2, '蛋白质', 'Protein', 'g', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (3, '脂肪', 'Fat', 'g', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (4, '碳水化合物', 'Carbohydrates', 'g', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (5, '纤维', 'Fiber', 'g', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (6, '糖', 'Sugar', 'g', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (7, '钠', 'Sodium', 'mg', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (8, '钾', 'Potassium', 'mg', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (9, '钙', 'Calcium', 'mg', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (10, '铁', 'Iron', 'mg', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (11, '维生素C', 'Vitamin C', 'mg', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (12, '维生素A', 'Vitamin A', 'IU', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (13, '胆固醇', 'Cholesterol', 'mg', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (14, '饱和脂肪', 'Saturated Fat', 'g', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (15, '单不饱和脂肪', 'Monounsaturated Fat', 'g', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (16, '多不饱和脂肪', 'Polyunsaturated Fat', 'g', 0, NULL, NULL);
INSERT INTO `nutrition_facts` (`nutri_id`, `nutri_name`, `nutri_name_en`, `unit`, `nutrition_id`, `nutrition_name`, `nutrition_name_en`) VALUES (17, '维生素D', 'Vitamin D', 'IU', 0, NULL, NULL);
COMMIT;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `gender` tinyint DEFAULT '1' COMMENT '0为未定义，1男，2女',
  `age` int DEFAULT '0',
  `weight` double DEFAULT '0',
  `height` double DEFAULT '0',
  `period` tinyint DEFAULT '0' COMMENT '0保持期，1减脂期，2增肌期',
  `target_calories` int DEFAULT '0',
  `register_date` date DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of users
-- ----------------------------
BEGIN;
INSERT INTO `users` (`user_id`, `username`, `password`, `gender`, `age`, `weight`, `height`, `period`, `target_calories`, `register_date`) VALUES (1, '1', '$2a$10$CeWmy8WSr4RRoJk2Hu1BV.wBxfOUIp6l0/CeoMtjsVmD3icSTILe6', 1, 26, 75, 180, 0, 2171, '2025-03-03');
INSERT INTO `users` (`user_id`, `username`, `password`, `gender`, `age`, `weight`, `height`, `period`, `target_calories`, `register_date`) VALUES (9, '2', '$2a$10$tDPUqHd2ROTsSDC2nOyz3urs5Y5CSdnH3q1IBrIiE.Elj/evmvbai', 0, 0, 0, 0, 0, 0, '2025-03-03');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
